USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_FINAL]    Script Date: 11/28/2011 12:00:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0005_FINAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0005_FINAL]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_FINAL]    Script Date: 11/28/2011 12:00:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0005_FINAL]
AS

BEGIN
			SELECT  
				Cuenta_Deudor = a.SCCTA,
				Dv_Rut_Deudor = substring(f.pfndoc, len(f.pfndoc), 1)  ,
				noperacion = a.SCOPER ,
				Patrimonio= a.sec01ptcta,
				Sucursal_Colocacion = A.Scsuc ,
				SALDO_CAPITAL = b.capital ,
				SALDO_INTERES = d.interes ,
				SALDO_REAJUSTE = c.reajuste ,
				Primer_Vencimiento_Impago = E.fecha_pago ,
				Ultimo_Vencimiento_Impago = E.ult_fecha ,
				Rubro_Bantotal = a.J50PCCBNT ,
				Rubro_PeopleSoft = a.J50PCCPS ,
				Marca_Fallecido= 
				CASE
					WHEN (f.Pffal = 'S') THEN 'S'
					ELSE 'N' 
				END,
				Marca_Securitizado = a.SECURITIZADO 	
			INTO
				#CRED_PRICE
			FROM
				base a
				LEFT JOIN capital b ON 	a.SCCTA = b.SCCTA AND a.SCOPER = b.SCOPER 
									AND a.SCRUB = B.SCRUB AND A.sCSUC = B.SCSUC 
									AND a.J50PCCBNT = B.J50PCCBNT AND a.J50PCCPS = B.J50PCCPS
				lEFT JOIN reajuste C ON a.SCCTA = C.SCCTA AND a.SCOPER = C.SCOPER 
									 AND A.sCSUC = C.SCSUC AND a.SCRUB = C.SCRUB 
									 AND a.J50PCCBNT = C.J50PCCBNT AND a.J50PCCPS = C.J50PCCPS 
				lEFT JOIN interes D ON a.SCCTA = D.SCCTA AND a.SCOPER = D.SCOPER AND A.sCSUC = D.SCSUC 
									AND	a.SCRUB = D.SCRUB AND a.J50PCCBNT = D.J50PCCBNT 
									AND	a.J50PCCPS = D.J50PCCPS 
				lEFT JOIN Prim_cuota_imp E ON A.SCCTA = E.CTA AND	A.SCOPER = E.OPER AND A.Scsuc = E.suc 
				,fsd002  f(nolock)
			WHERE
				--a.SCCTA *= b.SCCTA AND
				--a.SCOPER *= b.SCOPER AND
				--a.SCRUB *= B.SCRUB AND
				--A.sCSUC *= B.SCSUC AND
				--a.J50PCCBNT *= B.J50PCCBNT AND
				--a.J50PCCPS *= B.J50PCCPS AND
				--a.SCCTA *= C.SCCTA AND
				--a.SCOPER *= C.SCOPER AND
				--A.sCSUC *= C.SCSUC AND	
				--a.SCRUB *= C.SCRUB AND
				--a.J50PCCBNT *= C.J50PCCBNT AND
				--a.J50PCCPS *= C.J50PCCPS AND
				--a.SCCTA *= D.SCCTA AND
				--a.SCOPER *= D.SCOPER AND
				--A.sCSUC *= D.SCSUC AND	
				--a.SCRUB *= D.SCRUB AND
				--a.J50PCCBNT *= D.J50PCCBNT AND
				--a.J50PCCPS *= D.J50PCCPS AND
				--A.SCCTA *= E.CTA AND
				--A.SCOPER *= E.OPER AND
				--A.Scsuc *= E.suc AND
				f.pfndoc <> '' and
				a.SCCTA = substring(f.pfndoc, 1, len(f.pfndoc)-1)
END

BEGIN
		SELECT  
			Cuenta_Deudor
			,Dv_Rut_Deudor 
			,noperacion 
			,Patrimonio
			,Sucursal_Colocacion 
			,SALDO_CAPITAL = SUM(SALDO_CAPITAL) 
			,SALDO_INTERES = SUM (SALDO_INTERES )
			,SALDO_REAJUSTE  = SUM(SALDO_REAJUSTE)
			,Primer_Vencimiento_Impago 
			,Ultimo_Vencimiento_Impago 
			,Rubro_Bantotal = max(Rubro_Bantotal) 
			,Rubro_PeopleSoft
			,Marca_Fallecido
			,Marca_Securitizado
		INTO
			dbo.CRED_PRICE2
		FROM 
			#CRED_PRICE  
		GROUP BY  
			Cuenta_Deudor
			,Dv_Rut_Deudor 
			,noperacion 
			,Patrimonio
			,Sucursal_Colocacion 
			,Primer_Vencimiento_Impago 
			,Ultimo_Vencimiento_Impago 
			,Marca_Fallecido 
			,Rubro_Bantotal
			,Rubro_PeopleSoft
			,Marca_Fallecido
			,Marca_Securitizado
END


BEGIN 
		SELECT  
			a.*,
			Fecha_Colocacion =
			CASE
			WHEN(JT73109FAP is not null)THEN JT73109FAP
			ELSE Aofval
			END
		INTO #temp
		FROM 
			CRED_PRICE2 a
			LEFT JOIN JT73109  e  (nolock) ON noperacion = e.JT73109FPA and Sucursal_Colocacion =  e.JT73109SUC
			LEFT JOIN Fsd010  c(nolock) ON noperacion = aooper and Sucursal_Colocacion = aosuc and Cuenta_Deudor = aocta 
		WHERE 
			--noperacion *= e.JT73109FPA and
			--Sucursal_Colocacion *=  e.JT73109SUC and
			--noperacion *=	aooper and
			--Sucursal_Colocacion *= aosuc and
			--Cuenta_Deudor *= aocta and
			aosbop = 0 
END

BEGIN
		SELECT  
			CUENTA_DEUDOR
			,Dv_Rut_Deudor
			,noperacion
			,Patrimonio
			,Sucursal_Colocacion
			,SALDO_CAPITAL
			,SALDO_INTERES
			,SALDO_REAJUSTE
			,Primer_Vencimiento_Impago 
			,Ultimo_Vencimiento_Impago 
			,Rubro_Bantotal
			,Rubro_PeopleSoft
			,Marca_Fallecido
			,Marca_Securitizado
			, fecha_colocacion = max(fecha_colocacion) 
		INTO
			dbo.Cred_Price_Final2
		FROM 
			#temp
		GROUP BY  CUENTA_DEUDOR
			,Dv_Rut_Deudor
			,noperacion
			,Patrimonio
			,Sucursal_Colocacion
			,SALDO_CAPITAL
			,SALDO_INTERES
			,SALDO_REAJUSTE
			,Primer_Vencimiento_Impago 
			,Ultimo_Vencimiento_Impago 
			,Rubro_Bantotal
			,Rubro_PeopleSoft
			,Marca_Fallecido
			,Marca_Securitizado
END
-----------------------------------------------

BEGIN
	UPDATE 	dbo.Cred_Price_Final2 SET SALDO_CAPITAL = 0 WHERE SALDO_CAPITAL is null 
END

-----------------------------------------------

BEGIN
	UPDATE 	dbo.Cred_Price_Final2 SET SALDO_INTERES = 0 WHERE SALDO_INTERES is null
END

-----------------------------------------------

BEGIN
	UPDATE 	dbo.Cred_Price_Final2 SET SALDO_REAJUSTE = 0 WHERE SALDO_REAJUSTE is null
END


GO

